package com.changil.nowonchangil.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.changil.nowonchangil.dto.BibleWord;

public class BibleWordManager extends BaseTableManager{
	private static BibleWordManager instance = null;
	
	public static BibleWordManager getsInstance() {
		if (instance == null) {
			synchronized (BibleWordManager.class) {
				if (instance == null) { 
					instance = new BibleWordManager();
				}
			}
		}
		return instance;
	}
	
	public ArrayList<BibleWord> select(String book, int fromChapter, int fromVerse, int toChapter, int toVerse) {
		String sql = "SELECT * FROM  "+BibleWordTableInfo.NAME
					+" WHERE "+BibleWordTableInfo.FIELD_NAME_ID
					+" BETWEEN ( "
					+" SELECT "+BibleWordTableInfo.FIELD_NAME_ID
					+" FROM "+BibleWordTableInfo.NAME
					+" WHERE "+BibleWordTableInfo.FIELD_NAME_BOOK+"= ?"
					+" AND "+BibleWordTableInfo.FIELD_NAME_CHAPTER+"= ?"
					+" AND "+BibleWordTableInfo.FIELD_NAME_VERSE+"= ?"
					+" ) AND ( "
					+" SELECT "+BibleWordTableInfo.FIELD_NAME_ID
					+" FROM "+BibleWordTableInfo.NAME
					+" WHERE "+BibleWordTableInfo.FIELD_NAME_BOOK+"= ?"
					+" AND "+BibleWordTableInfo.FIELD_NAME_CHAPTER+"= ?"
					+" AND "+BibleWordTableInfo.FIELD_NAME_VERSE+"= ?"
					+" )"
					+" ORDER BY "+BibleWordTableInfo.FIELD_NAME_ID + " ASC";
		Connection conn = createConnection();
		PreparedStatement statement = null;
		ResultSet result = null;
		ArrayList<BibleWord> list = new ArrayList<BibleWord>(); 
		try {
			statement = conn.prepareStatement(sql);
			statement.setString(1, book);
			statement.setInt(2, fromChapter);
			statement.setInt(3, fromVerse);
			statement.setString(4, book);
			statement.setInt(5, toChapter);
			statement.setInt(6, toVerse);
			result = statement.executeQuery();
			 
			while (result.next()){
				BibleWord bibleWord = new BibleWord();
				bibleWord.id = result.getInt(result.findColumn(BibleWordTableInfo.FIELD_NAME_ID));
				bibleWord.book = result.getString(BibleWordTableInfo.FIELD_NAME_BOOK);
				bibleWord.chapter = result.getInt(result.findColumn(BibleWordTableInfo.FIELD_NAME_CHAPTER));
				bibleWord.verse = result.getInt(result.findColumn(BibleWordTableInfo.FIELD_NAME_VERSE));
				bibleWord.content = result.getString(result.findColumn(BibleWordTableInfo.FIELD_NAME_CONTENT));
			    list.add(bibleWord);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(null != result) {
				try {
					result.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		return list;
	}
	
//	public void insert(String content) throws SQLException{
//		String sql = "INSERT INTO "
//					+HistoryTableInfo.NAME
//					+"("+HistoryTableInfo.FIELD_NAME_CONTENT+")"
//					+"VALUES (?)";
//		
//		PreparedStatement statement = DbConnectManager.getsInstance().getConnection().prepareStatement(sql);
//		statement.setString(1, content);
//		int rowsInserted = statement.executeUpdate();
//		if (rowsInserted > 0) {
//		    System.out.println("A new user was inserted successfully!");
//		}
//	}
//	
//	public void update(int targetId, String newContent) throws SQLException {
//		String sql = "UPDATE "
//					+HistoryTableInfo.NAME
//					+" SET "
//					+HistoryTableInfo.FIELD_NAME_CONTENT+"=? "
//					+"WHERE "
//					+HistoryTableInfo.FIELD_NAME_ID+"=?";
//		 
//		PreparedStatement statement = DbConnectManager.getsInstance().getConnection().prepareStatement(sql);
//		statement.setString(1, newContent);
//		statement.setInt(2, targetId);
//		int rowsUpdated = statement.executeUpdate();
//		if (rowsUpdated > 0) {
//		    System.out.println("An existing user was updated successfully!");
//		}
//	}
//	
//	public void delete(int targetId) throws SQLException{
//		String sql = "DELETE FROM "
//					+HistoryTableInfo.NAME
//					+" WHERE "
//					+HistoryTableInfo.FIELD_NAME_ID+"=?";
//		 
//		PreparedStatement statement = DbConnectManager.getsInstance().getConnection().prepareStatement(sql);
//		statement.setInt(1, targetId);
//		 
//		int rowsDeleted = statement.executeUpdate();
//		if (rowsDeleted > 0) {
//		    System.out.println("A user was deleted successfully!");
//		}
//	}

}
